//
//  DataBaseHandler.m
//  A段项目音乐播放
//
//  Created by dllo on 15/6/1.
//  Copyright (c) 2015年 lanou3g.com蓝鸥科技. All rights reserved.
//

#import "DataBaseHandler.h"
#import "MusicListViewController.h"
@implementation DataBaseHandler

+ (DataBaseHandler *)shareInstance
{
    //static 一个应用程序执行期间, 只会执行一次
    static DataBaseHandler *dbHandler = nil;
    
    
    if (dbHandler == nil) {
        //如果指针指向空, 就创建一个对象
        dbHandler = [[DataBaseHandler alloc] init];
        
        [dbHandler openDB];
//        [dbHandler createTable];
        
    }
    
    return dbHandler;
    
}


- (void)openDB
{
    
    //打开数据库的函数:
    
    
    NSString *docPath = [NSSearchPathForDirectoriesInDomains(NSLibraryDirectory, NSUserDomainMask, YES) lastObject];
    //拼接一个数据库文件路径
    NSString *dbPath = [docPath stringByAppendingPathComponent:@"dataBase.db"];
    
    //参数1:数据库文件的路径
    //参数2:数据库指针的地址
    
    int result = sqlite3_open([dbPath UTF8String], &dbPoint);
    
    NSFileManager *manager = [NSFileManager defaultManager];
    if (![manager fileExistsAtPath:dbPath]) {
        NSString *source = [[NSBundle mainBundle]pathForResource:@"dataBase.db" ofType:nil];
        [manager copyItemAtPath:source toPath:dbPath error:nil];
    }
    
    //    SQLITE_OK  错误码
    
    NSLog(@"执行结果: %d", result);
    NSLog(@"路径: %@", dbPath);
}

- (void)closeDB
{
    //关闭数据库
    sqlite3_close(dbPoint);
}


- (void)createTableWithString:(NSString *)str
{
    //执行SQL语句的函数
    
    //参数1:数据库指针
    //参数2:sql语句
    
    
    
    NSString *sql = [NSString stringWithFormat:@"create table %@ (name text, singerName text , url text,songId text primary key)", str];
    int result = sqlite3_exec(dbPoint, [sql UTF8String], NULL, NULL, NULL);
    
//    NSLog(@"创建表结果: %d", result);
    if (result == SQLITE_OK) {
        NSLog(@"创建表成功");
    }else{
        NSLog(@"创建表失败");
    }
    
}


- (void)insertmodel:(SingerModel *)model str:(NSString *)str
{
    NSString *sql = [NSString stringWithFormat:@"insert into %@ values('%@', '%@', '%@', '%@')",str,model.name, model.singerName, model.url,model.songId];
    
    int result = sqlite3_exec(dbPoint, [sql UTF8String], NULL, NULL, NULL);
    
    // 判断是否执行成功
    if (result == SQLITE_OK) {
        NSLog(@"插入成功");
    }else{
        NSLog(@"插入失败");
    }
    
}


- (NSArray *)selectAllWithstr:(NSString *)str
{
    //1.创建一个数据库指针的替身
    //替身作为一个临时的数据库指针, 保存所有对数据库的操作, 最终确认无误后写入本地数据库
    
    sqlite3_stmt *stmt = nil;
    
    //2.检查sql语句, 准备执行
    //作用:把替身和数据库指针连接起来
    
    //参数1:数据库指针
    //参数2:sql语句
    NSString *sql = [NSString stringWithFormat:@"select * from %@",str];
    //参数3:限制sql语句的长度(-1为不限制)
    //参数4:替身的指针
    int result = sqlite3_prepare_v2(dbPoint, [sql UTF8String], -1, &stmt, NULL);
    
    //对sql语句的检查结果进行判断
    if (result == SQLITE_OK) {
        
        NSMutableArray *array = [NSMutableArray array];
        
        //当符合sql语句查询条件的结果有多行, 就执行循环体的代码
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            //参数1:替身
            //参数2:取的是第几列
        
            const unsigned char *name = sqlite3_column_text(stmt, 0);
            const unsigned char *singerName = sqlite3_column_text(stmt, 1);
            const unsigned char *url = sqlite3_column_text(stmt, 2);
            const unsigned char *songId = sqlite3_column_text(stmt, 3);
            
            NSString *name1 = [NSString stringWithUTF8String:(const char *)name];
            NSString *singerName1 = [NSString stringWithUTF8String:(const char *)singerName];
            NSString *url1 = [NSString stringWithUTF8String:(const char *)url];
            NSString *songId1 = [NSString stringWithUTF8String:(const char *)songId];
            
            //把拿到的nsstring数据给一个学生对象赋值
            
            SingerModel *model = [[SingerModel alloc]init];
           
            model.name = name1;
            model.singerName = singerName1;
            model.url = url1;
            model.songId = songId1;
            
            //把创建好的学生对象添加到数组中
            [array addObject:model];
            [model release];
            
        }
        //释放替身的内存占用, 将替身的所有操作应用到底层数据库文件
        sqlite3_finalize(stmt);
        return array;
    }
    //若失败,返回空
    sqlite3_finalize(stmt);
    return nil;
}


// 找 数据库 已经存 的表
- (NSArray *)selecttableName
{
    NSMutableArray *arr = [NSMutableArray array];
    
    NSString *docPath = [NSSearchPathForDirectoriesInDomains(NSLibraryDirectory, NSUserDomainMask, YES) lastObject];
    //拼接一个数据库文件路径
    NSString *dbPath = [docPath stringByAppendingPathComponent:@"dataBase.db"];

    
    sqlite3 *database;
    sqlite3_open([dbPath UTF8String], &database);
    
    sqlite3_stmt *statement;
    const char *getTableInfo = "select * from sqlite_master where type='table' order by name";
    sqlite3_prepare_v2(database, getTableInfo, -1, &statement, nil);
    
    while (sqlite3_step(statement) == SQLITE_ROW) {
        char *nameData = (char *)sqlite3_column_text(statement, 1);
        NSString *tableName = [[NSString alloc] initWithUTF8String:nameData];
        NSLog(@"name:%@",tableName);
        
        [arr addObject:tableName];
        
    }
    return arr;
}


- (void)deleteWithtable:(NSString *)strr
{
    
    NSString *sql = [NSString stringWithFormat:@"drop table '%@'",strr];
    
    int result = sqlite3_exec(dbPoint, [sql UTF8String], NULL, NULL, NULL);
    
    if (result == SQLITE_OK) {
        NSLog(@"删除成功");
    }else{
        NSLog(@"删除失败");
    }
}

- (void)deleteWithModel:(SingerModel *)model tablename:(NSString *)strr
{
    NSString *str = [NSString stringWithFormat:@"delete from '%@' where name = '%@'",strr,model.name];
    
    int result = sqlite3_exec(dbPoint, [str UTF8String], nil, nil, nil);
    
    if (result == SQLITE_OK) {
        NSLog(@"删除成功");
    }else{
        NSLog(@"删除失败");
    }
}




@end
